package com.aizhixin.lab.project.course.service;

import cn.hutool.core.date.DateUtil;
import com.aizhixin.lab.common.core.ApiReturnConstants;
import com.aizhixin.lab.common.core.DataValidity;
import com.aizhixin.lab.common.dto.SortDTO;
import com.aizhixin.lab.common.utils.ExcelUtil;
import com.aizhixin.lab.common.utils.PageJdbcUtil;
import com.aizhixin.lab.company.repository.UserRepository;
import com.aizhixin.lab.project.course.domain.*;
import com.aizhixin.lab.project.course.entity.*;
import com.aizhixin.lab.project.course.repository.*;
import com.aizhixin.lab.project.course.vo.*;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Slf4j
@Service
@Transactional
public class ProjectClassStuService {
    @Autowired
    private PageJdbcUtil pageJdbcUtil;
    @Autowired
    private ProjectClassRepository classRepository;
    @Autowired
    private ProjectClassStuRepository stuRepository;
    @Autowired
    private ProjectGroupRepository groupRepository;
    @Autowired
    private UserRepository userRepository;
    @Autowired
    private ProjectGroupService groupService;

    RowMapper<ProjectClassStutVo> documentVoRowMapper = new RowMapper<ProjectClassStutVo>() {

        @Override
        public ProjectClassStutVo mapRow(ResultSet rs, int rowNum) throws SQLException {

            ProjectClassStutVo domain = new ProjectClassStutVo();
            domain.setId(rs.getString("id"));
            domain.setStuId(rs.getString("stu_id"));
            domain.setRole(rs.getString("role"));
            domain.setStuName(rs.getString("stu_name"));
            domain.setRoleName(rs.getString("role_name"));
            return domain;
        }
    };


    public List<ProjectClassStutVo> findProjectGroupMember(QueryProjectGroupMemberDomain domain) {
        String sql = "SELECT  a.id,a.stu_id,a.role,b.name as stu_name,c.name as role_name FROM `c_project_class_stu` a left join t_user b on a.stu_id = b.id left join t_project_role c on a.role_id = c.id where a.project_id = '" + domain.getProjectId() + "'and a.group_id = '" + domain.getGroupId() + "'";
        List<ProjectClassStutVo> projectWeeklyList = pageJdbcUtil.getInfo(sql, documentVoRowMapper);
        return projectWeeklyList;
    }

    public List<ProjectClassStutVo> findMarkMember(QueryProjectGroupMemberDomain domain) {
        String sql = "SELECT a.stu_id,a.role,b.name as stu_name FROM `c_project_class_stu` a left join t_user b on a.stu_id = b.id where a.project_id = '" + domain.getProjectId() + "'and a.group_id = '" + domain.getGroupId() + "'";
        List<ProjectClassStutVo> projectWeeklyList = pageJdbcUtil.getInfo(sql, documentVoRowMapper);
        return projectWeeklyList;
    }

    /**
     * @param projectClassStuId 项目班级学生ID
     * @param type              10:点赞 20:督促
     * @return
     */
    public Map<String, Object> likeOrUrge(String projectClassStuId, Integer type) {
        Map<String, Object> result = new HashMap<>();
        ProjectClassStu projectClassStu = stuRepository.findOne(projectClassStuId);
        if (Objects.isNull(projectClassStu)) {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "未查询该学生");
            return result;
        }
        if (type == 10) {
            projectClassStu.setLikeNum(projectClassStu.getLikeNum() + 1);
        } else {
            projectClassStu.setUrgeNum(projectClassStu.getUrgeNum() + 1);
        }
        stuRepository.save(projectClassStu);
        result.put(ApiReturnConstants.SUCCESS, true);
        return result;

    }

    public Map<String, Object> urge(String projectId, Long stuId) {
        Map<String, Object> result = new HashMap<>();
        ProjectClassStu projectClassStu = stuRepository.findByProjectIdAndStuIdAndDeleteFlag(projectId, stuId, DataValidity.VALID.getState());
        if (Objects.isNull(projectClassStu)) {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "未查询该学生");
            return result;
        }
        projectClassStu.setUrgeNum(projectClassStu.getUrgeNum() + 1);
        stuRepository.save(projectClassStu);
        result.put(ApiReturnConstants.SUCCESS, true);
        return result;
    }

    /**
     * 删除学生
     *
     * @param stuId
     * @return
     */
    public Map<String, Object> deleteStu(List<String> stuId) {
        Map<String, Object> result = new HashMap<>();
        List<ProjectClassStu> all = stuRepository.findAll(stuId);
        List<ProjectClassStu> stuList = new ArrayList<>();
        for (ProjectClassStu projectClassStu : all) {
            projectClassStu.setDeleteFlag(DataValidity.INVALID.getState());
            stuList.add(projectClassStu);
        }
        stuRepository.save(stuList);
//        if (stuList.size()>0){
//            ProjectClassStu projectClassStu = stuList.get(0);
//            ProjectClass one = classRepository.findOne(projectClassStu.getProjectClassId());
//            if (Objects.isNull(one)){
//                one.setStuNum(stuRepository.countByProjectClassIdAndDeleteFlag(one.getId(),DataValidity.VALID.getState()));
//            }
//        }
        result.put(ApiReturnConstants.SUCCESS, true);
        return result;
    }

    /**
     * 删除班级
     *
     * @param id
     * @return
     */
    public Map<String, Object> deleteClass(String id) {
        Map<String, Object> result = new HashMap<>();
        ProjectClass one = classRepository.findOne(id);
        if (!Objects.isNull(one)) {
            List<ProjectGroup> projectGroupList = groupRepository.findByProjectIdAndDeleteFlag(one.getProjectId(), DataValidity.VALID.getState());
            if (projectGroupList.size() > 0) {
                result.put(ApiReturnConstants.SUCCESS, false);
                result.put(ApiReturnConstants.ERROR, "请先删除小组信息");
                return result;
            }
        }
        List<ProjectClassStu> projectClassStuList = stuRepository.findByProjectClassIdAndDeleteFlag(id, DataValidity.VALID.getState());
        one.setDeleteFlag(DataValidity.INVALID.getState());
        List<ProjectClassStu> stuList = new ArrayList<>();
        for (ProjectClassStu projectClassStu : projectClassStuList) {
            projectClassStu.setDeleteFlag(DataValidity.INVALID.getState());
            stuList.add(projectClassStu);
        }
        classRepository.save(one);
        stuRepository.save(stuList);
        result.put(ApiReturnConstants.SUCCESS, true);
        return result;
    }

    RowMapper<ProjectGroupVo> groupVoRowMapper = new RowMapper<ProjectGroupVo>() {

        @Override
        public ProjectGroupVo mapRow(ResultSet rs, int rowNum) throws SQLException {

            ProjectGroupVo domain = new ProjectGroupVo();
            domain.setId(rs.getString("id"));
            domain.setGroupName(rs.getString("group_name"));
            domain.setGroupNum(rs.getInt("group_num"));
            domain.setUnCommit(rs.getInt("uncommit"));
            domain.setTotalTask(rs.getInt("total_task"));
            return domain;
        }
    };

    /**
     * 查询项目下的小组列表
     *
     * @param domain
     * @return
     */
    public Map<String, Object> findProjectGroup(QueryGroupDomain domain) {
        String sql = "SELECT c.id, c.group_name,c.group_num,(SELECT COUNT(*) FROM c_project_group_task cp WHERE cp.group_id=c.id AND cp.task_status in('init','running','timeout')) uncommit,(SELECT COUNT(*) FROM c_project_group_task cp WHERE cp.group_id=c.id) total_task FROM c_project_group c WHERE c.project_id= '" + domain.getProjectId() + "' and c.delete_flag=0";
        String countSql = "SELECT COUNT(*) FROM c_project_group c WHERE c.project_id= '" + domain.getProjectId() + "' and c.delete_flag=0";
        List<SortDTO> sort = new ArrayList<SortDTO>();
        SortDTO dto = new SortDTO();
        dto = new SortDTO();
        dto.setKey("c.created_date");
        dto.setAsc(true);
        sort.add(dto);
        Map<String, Object> pageInfor = pageJdbcUtil.getPageInfor(domain.getPageSize(), domain.getPageNumber(), groupVoRowMapper, sort, sql, countSql);
        List<ProjectGroupVo> list = (List<ProjectGroupVo>) pageInfor.get(ApiReturnConstants.DATA);

        list.forEach(item -> {
            try {
                String string = pageJdbcUtil.getString("SELECT c.project_id FROM c_project_class c LEFT JOIN c_project p ON c.project_id=p.id WHERE c.group_id='" + item.getId() + "' AND p.project_type='备选项目' AND c.delete_flag=0 AND p.delete_flag=0");
                item.setBftProjectId(string);
            } catch (EmptyResultDataAccessException e) {
            }
            try {
                String string1 = pageJdbcUtil.getString("SELECT c.project_id FROM c_project_class c LEFT JOIN c_project p ON c.project_id=p.id WHERE c.group_id='" + item.getId() + "' AND p.project_type='练习项目' AND c.delete_flag=0 AND p.delete_flag=0");
                item.setTestProjectId(string1);
            } catch (EmptyResultDataAccessException e) {

            }
        });
        pageInfor.put(ApiReturnConstants.DATA, list);
        return pageInfor;
    }

    RowMapper<ProjectGroupMemberVo> memberVoRowMapper = new RowMapper<ProjectGroupMemberVo>() {

        @Override
        public ProjectGroupMemberVo mapRow(ResultSet rs, int rowNum) throws SQLException {

            ProjectGroupMemberVo domain = new ProjectGroupMemberVo();
            domain.setId(rs.getString("id"));
            domain.setJob(rs.getString("JOB_NUM"));
            domain.setRole(rs.getString("role"));
            domain.setName(rs.getString("name"));
            domain.setLabel(rs.getString("PERSONAL_PROFILE"));
            domain.setLike(rs.getInt("like_num"));
            domain.setUrge(rs.getInt("urge_num"));
            domain.setStuId(rs.getLong("stu_id"));
            domain.setProjectId(rs.getString("project_id"));
            return domain;
        }
    };

    /**
     * 查看小组成员
     *
     * @param groupId
     * @return
     */
    public List<ProjectGroupMemberVo> findProjectGroupMember(String groupId, String projectId) {
        String sql = "SELECT cpcs.id,tu.JOB_NUM,cpcs.stu_id,cpcs.project_id,tu.`name`,cpcs.role,tu.PERSONAL_PROFILE,cpcs.like_num,cpcs.urge_num FROM c_project_class_stu cpcs LEFT JOIN t_user tu ON cpcs.stu_id=tu.id  WHERE cpcs.delete_flag=0 and cpcs.group_id='" + groupId + "' AND cpcs.project_id='" + projectId + "' ORDER BY tu.id ASC";
        return pageJdbcUtil.getInfo(sql, memberVoRowMapper);
    }

    RowMapper<ProjectGroupTaskCommitVo> commitVoRowMapper = new RowMapper<ProjectGroupTaskCommitVo>() {

        @Override
        public ProjectGroupTaskCommitVo mapRow(ResultSet rs, int rowNum) throws SQLException {

            ProjectGroupTaskCommitVo domain = new ProjectGroupTaskCommitVo();

            domain.setName(rs.getString("name"));
            domain.setCommitNum(rs.getInt("commit_num"));
            domain.setTotalNum(rs.getInt("total_num"));
            return domain;
        }
    };

    /**
     * 查看小组成员任务完成情况
     *
     * @param groupId
     * @return
     */
    public List<ProjectGroupTaskCommitVo> findProjectGroupTaskCommit(String groupId) {
        String sql = "SELECT tu.`name`,(SELECT COUNT(*) FROM c_project_group_task cpg WHERE cpg.stu_id=cpc.stu_id AND cpg.project_id=cpc.project_id) total_num,(SELECT COUNT(*) FROM c_project_group_task cpg WHERE cpg.stu_id=cpc.stu_id AND cpg.project_id=cpc.project_id AND cpg.reality_end_date is NOT NULL) commit_num FROM c_project_class_stu cpc LEFT JOIN t_user tu ON cpc.stu_id=tu.id WHERE cpc.group_id='" + groupId + "'";
        return pageJdbcUtil.getInfo(sql, commitVoRowMapper);
    }

    public HSSFWorkbook getProjectStuList(String projectId) {
        List<ProjectClassStu> projectClassStuList = stuRepository.findByProjectIdAndDeleteFlagOrderByGroupId(projectId, DataValidity.VALID.getState());
        List<ProjectGroup> projectGroupList = groupRepository.findByProjectIdAndDeleteFlag(projectId, DataValidity.VALID.getState());
        Map<String, String> map = new HashMap<>();
        projectGroupList.forEach(item -> {
            map.put(item.getId(), item.getGroupName());
        });
        HSSFWorkbook wb = new HSSFWorkbook();
//        XSSFWorkbook xssfSheets = new XSSFWorkbook();
//        XSSFSheet sheet1 = xssfSheets.createSheet();
        HSSFSheet sheet = wb.createSheet("学生分组列表");

        HSSFRow row1 = sheet.createRow(0);
        row1.createCell(0).setCellValue("序号");
        row1.createCell(1).setCellValue("学生唯一标识");
        row1.createCell(2).setCellValue("姓名");
        row1.createCell(3).setCellValue("小组名");

        int i = 1;
        for (ProjectClassStu projectClassStu : projectClassStuList) {
            HSSFRow row = sheet.createRow(i);
            row.createCell(0).setCellValue(i);
            row.createCell(1).setCellValue(projectClassStu.getId());
            row.createCell(2).setCellValue(userRepository.findByIdAndDeleteFlag(projectClassStu.getStuId(), DataValidity.VALID.getState()).getName());
            row.createCell(3).setCellValue(map.get(projectClassStu.getGroupId()));
            i++;
        }

        return wb;
    }

    public Map<String,Object> importProjectStuList(MultipartFile file, String projectId) throws IOException {
        ExcelUtil excelUtil = new ExcelUtil(file.getInputStream(), "2003");
        Sheet sheetAt = excelUtil.getWorkbook().getSheetAt(0);
        int lastRowNum = sheetAt.getLastRowNum();
        Map<String,String> map=new HashMap<>();
        Map<String,Object> result=new HashMap<>();
        List<ProjectClassStu> stuList=new ArrayList<>();
        List<ProjectGroup> projectGroupList = groupRepository.findByProjectIdAndDeleteFlag(projectId, DataValidity.VALID.getState());
        projectGroupList.forEach(item->{
            map.put(item.getGroupName(),item.getId());
        });
        for (int i=1;i<=lastRowNum;i++){
            Row row = sheetAt.getRow(i);
            if (StringUtils.isEmpty(excelUtil.getCellValueToString(row.getCell(1)))){
                continue;
            }
            ProjectClassStu stu = stuRepository.findOne(excelUtil.getCellValueToString(row.getCell(1)).trim());
            if (Objects.isNull(stu)){
                result.put(ApiReturnConstants.SUCCESS,false);
                result.put(ApiReturnConstants.MESSAGE,"学生唯一标识被修改,请重新导出学生列表确认");
                return result ;
            }
            if (!stu.getProjectId().equals(projectId)){
                result.put(ApiReturnConstants.SUCCESS,false);
                result.put(ApiReturnConstants.MESSAGE,"请不要把其他项目的分组表在此处导入");
                return result ;
            }
            String s = map.get(excelUtil.getCellValueToString(row.getCell(3)).trim());
            if (StringUtils.isEmpty(s)){
                ProjectGroup projectGroup = new ProjectGroup();
                projectGroup.setId(UUID.randomUUID().toString());
                projectGroup.setGroupName(excelUtil.getCellValueToString(row.getCell(3)).trim());
                projectGroup.setProjectId(projectId);
                projectGroup.setMaxNum(8);
                groupRepository.save(projectGroup);
                map.put(projectGroup.getGroupName(),projectGroup.getId());
                stu.setGroupId(projectGroup.getId());
                stu.setRole("member");
            }else {
                stu.setGroupId(s);
                stu.setRole("member");
            }
            stuList.add(stu);
        }
        groupService.updateGroupNum(projectId);
        stuRepository.save(stuList);
        result.put(ApiReturnConstants.SUCCESS,false);
        result.put(ApiReturnConstants.MESSAGE,"分组成功");
        return result ;
    }


}
